package com.monster.dao;

import com.monster.pojo.Album;
import com.monster.pojo.Singer;
import com.monster.pojo.Song;

import javax.xml.transform.Result;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SingerDaoImpl extends BaseDao implements SingerDao {
    ResultSet rs = null;

      /**
     * 显示所有的歌手，通过歌手热度降序排列
     * @param
     * @return Singers姓名和海报url
    **/

    @Override
    public ArrayList<Singer> getAllSingers(int page) {
        Singer singer = null;
        ArrayList<Singer> singerList = new ArrayList<Singer>();
        String sql = "SELECT singer_id,singer_name,singer_poster FROM singer ORDER BY singer_hot DESC limit ?,60";
        Object[] params = {(page-1)*60};
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                singer = new Singer();
                singer.setSingerId(rs.getLong("singer_id"));
                singer.setSingerName(rs.getString("singer_name"));
                singer.setPoster(rs.getString("singer_poster"));
                singerList.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singerList;
    }

    /**
     * 通过中文模糊查询，通过歌手热度降序排列
     * @param letter
     * @return Singers
    **/

    /**
     * 通过中文模糊查询，通过歌手热度降序排列
     * @param letter
     * @return Singers
     **/

    @Override
    public ArrayList<Singer> getSingersByFuzzy(String letter) {
        Singer singer = null;
        ArrayList<Singer> singerList = new ArrayList<Singer>();
        String sql = "SELECT singer_id,singer_name,singer_poster FROM singer where singer_name LIKE '%" + letter + "%' ORDER BY singer_hot DESC";
        Object[] params =null;
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                singer = new Singer();
                singer.setSingerId(rs.getLong("singer.singer_id"));
                singer.setSingerName(rs.getString("singer.singer_name"));
                singer.setPoster(rs.getString("singer_poster"));
                singerList.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singerList;
    }

/**
     * 通过首字母查询，通过歌手热度降序排列
     * @param firstLetter
     * @return Singers

**/
    @Override
    public ArrayList<Singer> getSingersByFirstLetter(String firstLetter,int page) {
        Singer singer = null;
        ArrayList<Singer> singerList = new ArrayList<Singer>();
        String sql = "SELECT singer_id,singer_name,singer_poster FROM singer where singer_firstletter=? ORDER BY singer_hot DESC limit ?,30";
        Object[] params = {firstLetter,(page-1)*30};
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                singer = new Singer();
                singer.setSingerId(rs.getLong("singer_id"));
                singer.setSingerName(rs.getString("singer_name"));
                singer.setPoster(rs.getString("singer_poster"));
                singerList.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singerList;
    }

/**
     * 通过歌手ID查询详情，通过歌曲热度降序排列
     * @param singerId
     * @return singerList
**/

    @Override
    public Singer getSingersBySingerId(Long singerId) {
        Singer singer = null;
        Album album = null;
        Song song = null;
        ArrayList<Album> albumList = new ArrayList<Album>();
        ArrayList<Song> songList = new ArrayList<Song>();
        String sql = "SELECT singer.singer_id,singer_name,singer.`singer_poster`,singer.`singer_area`,singer.`singer_profile`," +
                "singer_hot,song.`song_id`,song.`song_name`,song_poster,song_url,song_lyric ,album.`album_id`,album.`album_name`,song.`song_time` " +
                "FROM singer LEFT JOIN song_singer ON singer.`singer_id` = song_singer.`singer_id` " +
                "LEFT JOIN song ON song_singer.`song_id` = song.`song_id` LEFT JOIN album ON song.`album_id` = album.`album_id`" +
                " WHERE singer.`singer_id`=? ORDER BY song.song_hot DESC";
        Object[] params = {singerId};
        rs = this.executeQuery(sql, params);
        singer = new Singer();
        try {
            while (rs.next()) {
                song=new Song();
                album=new Album();
                //歌手信息
                singer.setSingerId(rs.getLong("singer.singer_id"));//ID
                singer.setSingerName(rs.getString("singer_name"));//歌手名字
                singer.setProfile(rs.getString("singer_profile"));//歌手简介
                singer.setArea(rs.getString("singer_area"));//歌手地区
                singer.setPoster(rs.getString("singer_poster"));//海报
                //歌手歌曲集合
                song.setSongId(rs.getLong("song.song_id"));//歌曲ID
                song.setSongName(rs.getString("song.song_name"));//歌曲名字
                song.setTime(rs.getString("song.song_time"));//歌曲时长
                song.setUrl(rs.getString("song_url"));
                song.setPoster(rs.getString("song_poster"));
                song.setLyric(rs.getString("song_lyric"));
                songList.add(song);
                //歌手专辑集合
                //album.setAlbumId(rs.getLong("album.album_id"));//专辑ID
                //album.setAlbumName(rs.getString("album.album_name"));//专辑名字
                //album.setAlbumPoster(rs.getString("album.album_poster"));//专辑海报
                //album.setAlbumPublicTime(rs.getString("album_public_time"));//专辑发布时间
                //albumList.add(album);

                singer.setSongs(songList);//歌曲集合
                //singer.setAlbums(albumList);//专辑集合
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singer;
    }

/**
     * 通过歌手ID查询歌手歌曲数量、专辑数量
     * @param singerId
     * @return songsAmount

**/
    @Override
    public Singer songsAlbumsAmount(Long singerId) {
        Singer singer=null;
        String sql="SELECT COUNT(song.`song_id`) AS songsAmount,(SELECT COUNT(singer_album.`album_id`) " +
                "FROM singer_album WHERE singer_album.`singer_id` = singer.`singer_id` ) AS albumAmount " +
                "FROM song LEFT JOIN song_singer ON song_singer.`song_id` = song.`song_id` " +
                "LEFT JOIN singer ON song_singer.`singer_id` = singer.`singer_id` WHERE singer.`singer_id`=?";
        Object[] params = {singerId};
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                singer=new Singer();
                singer.setSongAmount(rs.getLong("songsAmount"));//单曲数量
                singer.setAlbumAmount(rs.getLong("albumAmount"));//专辑数量
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singer;
    }

/**
     * 通过歌手名字首字母/地区/性别/流派查询歌手
     * @param firstLetter/area/sex/genre
     * @return ArrayList<Singer>

**/
    @Override
    public ArrayList<Singer> getSingersByOption(String firstLetter, String area, String sex, String genre,int page) {
        Singer singer = null;
        ArrayList<Singer> singerList = new ArrayList<Singer>();
        //集合接收传过来的参数
        ArrayList<Object> pList = new ArrayList<Object>();
        String sql = "SELECT singer_id,singer_name,singer_poster FROM singer where 1=1 ";
        if(firstLetter!=null){
            sql += " and singer.singer_firstLetter = ?";
            pList.add(firstLetter);
        }
        if(area!=null){
            sql += " and singer.singer_area = ? ";
            pList.add(area);
        }
        if(sex!=null){
            sql += " and singer.singer_sex = ? ";
            pList.add(sex);
        }
        if(genre!=null){
            sql += " and singer.singer_genre = ?";
            pList.add(genre);
        }
        if(page>0){
            sql += "order by singer_hot DESC limit ?,5";
            pList.add((page-1)*5);
        }
        //根据接收到的参数定义数组长度
        Object[] params = new Object[pList.size()];
        for(int i =0;i<pList.size();i++){
            params[i]=pList.get(i);
        }
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                singer = new Singer();
                singer.setSingerId(rs.getLong("singer_id"));
                singer.setSingerName(rs.getString("singer_name"));
                singer.setPoster(rs.getString("singer_poster"));
                singerList.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singerList;
    }

/**
     * 通过focus_type=歌手 ，focus_id=歌手ID 查询歌手的关注数
     * @param singerId
     * @return
**/

    public int selectFocusBySingerId(Long singerId){
        String sql = "SELECT COUNT(1) FROM user_focus WHERE focus_type=1 and focus_id=?";
        Object[] obj = {singerId};
        rs=this.executeQuery(sql,obj);
        int count =0;
        try {
            while(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


/**
     * 通过歌手ID  查询歌手的专辑集合
**/

    public List<Album> selectAlbumBySingerId(Long singerId){
        List<Album> list = new ArrayList<Album>();
        String sql = "select * from album,singer_album where album.album_id=singer_album.album_id and singer_album.singer_id=?";
        Object[] obj = {singerId};
        rs = this.executeQuery(sql,obj);
        Album album = null;
        try {
            while(rs.next()){
                album = new Album();
                album.setAlbumId(rs.getLong("album_id"));//专辑ID
                album.setAlbumName(rs.getString("album_name"));//专辑名字
                album.setAlbumPoster(rs.getString("album_poster"));//专辑海报
                album.setAlbumPublicTime(rs.getString("album_public_time"));//发行时间
                list.add(album);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  list;
    }

/**
     * 通过地区找到歌手，返回该地区歌曲
     * @param area,page
     * @param page
     * @return
**/

    @Override
    public ArrayList<Singer> getSingersSongsByArea(String area, int page) {
        ArrayList<Singer> alList = new ArrayList<Singer>();
        String sql = "SELECT singer.singer_id, singer.singer_name,song_name, song.song_id,song_time,song.`song_poster` FROM singer \n" +
                "LEFT JOIN song_singer ON singer.singer_id = song_singer.`singer_id` LEFT JOIN song ON song.`song_id` = song_singer.`song_id` \n" +
                "WHERE singer.`singer_area` = ? GROUP BY SINGER.`singer_id` HAVING COUNT(*)>0 ORDER BY song.`song_public_time` DESC LIMIT ?, 12 ";

       Object[] params = {area,(page-1)*12};
       rs = this.executeQuery(sql,params);
       Song song =null;
       Singer singer = null;
        try {
            while(rs.next()){
                song = new Song();
                singer = new Singer();

                song.setSongId(rs.getLong("song.song_id"));
                song.setSongName(rs.getString("song.song_name"));
                song.setTime(rs.getString("song.song_time"));
                song.setPoster(rs.getString("song.song_poster"));

                singer.setSingerId(rs.getLong("singer.singer_id"));
                singer.setSingerName(rs.getString("singer.singer_name"));
                singer.setSong(song);
                alList.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return alList;
    }


    /**
     * 通过歌手地区，查询歌手专辑
     * @param area,page
     * @return 歌手最新歌曲，歌手不重复
     */
    @Override
    public ArrayList<Singer> getSingersAlbumsByArea(String area, int page) {
        ArrayList<Singer> alList = new ArrayList<Singer>();
        String sql = "SELECT singer.singer_id, singer.singer_name,album_name, album.`album_id`,album.`album_poster` " +
                "FROM singer LEFT JOIN singer_album ON singer.singer_id = singer_album.`singer_id` " +
                "LEFT JOIN album ON album.`album_id` = singer_album.`album_id` WHERE singer.`singer_area` = ? " +
                "GROUP BY SINGER.`singer_id` HAVING COUNT(*)>0 ORDER BY album.`album_hot` DESC LIMIT ?,8";
        Object[] params = {area,(page-1)*6};
        rs = this.executeQuery(sql,params);
        Album album =null;
        Singer singer = null;
        try {
            while(rs.next()){
                album = new Album();
                singer = new Singer();
                album.setAlbumId(rs.getLong("album.album_id"));
                album.setAlbumName(rs.getString("album.album_name"));
                album.setAlbumPoster(rs.getString("album.album_poster"));
                singer.setSingerName(rs.getString("singer.singer_name"));
                singer.setSingerId(rs.getLong("singer.singer_id"));
                singer.setAlbum(album);
                alList.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return alList;
    }

    @Override
    public ArrayList<Song> getSongsBySingerId(Long singerId) {
        ArrayList<Song> songlist = new ArrayList<>();
        String sql = "SELECT * FROM singer,song_singer WHERE singer.`singer_id`=song_singer.`singer_id` AND singer.singer_id=?";
        Object[] obj = {singerId};
        rs = this.executeQuery(sql,obj);
        Song song = null;
        try {
            while(rs.next()){
                song = new Song();
                song.setSongId(rs.getLong("song_singer.song_id"));
                songlist.add(song);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  songlist;

    }

}
